﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.IO;
using Transcend.FileUploader.Entity;
using Transcend.FileUploader.Utility;
using System.Data.Common;
using Transcend.Archive.CSScan.Store.Contract;

namespace Transcend.FileUploader.Dao
{

    /// <summary>
    /// 扫描库数据库操作类，仅对xxscan数据表结构有效
    /// 该类不是线程安全的，请不要使用于多线程环境
    /// </summary>
    public class StoreDocument
    {

        private static readonly DocInstanceMapper instMapper = new DocInstanceMapper();
        private static readonly DocPageMapper pageMapper = new DocPageMapper();
        private static readonly DocCategoryMapper cateMapper = new DocCategoryMapper();
        private ImageStore ImageStorage;

        public DocStorageAppType AppType { get; set; }

        /// <summary>
        /// 图像文件是否是加密文件
        /// </summary>
        public bool EncryptedImage { get; set; }
        /// <summary>
        /// 图像加密密码
        /// </summary>
        public string ImagePassword { get; set; }
        public Transcend.DP3.Db.Database Db { get; set; }

        public StoreDocument()
        {

        }

        public StoreDocument(DP3.Db.Database db)
            : this()
        {
            Db = db;
        }

        #region --------------------------- 原文删除

        /// <summary>
        /// 删除整个原文数据
        /// </summary>
        public void DeleteAllDocPage(decimal instId)
        {
            var sql = "select b.ID from DS_Instance a, DS_Item b where a.ID = b.Inst_Id and a.ID=:instid";
            using (var cmd = Db.mkCommand(sql, instId))
            {
                using (var dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        DeleteCategory(instId, Transcend.DP3.Db.Database.GetValue<decimal>(dr, "ID"));
                    }
                }
            }
        }

        /// <summary>
        /// 删除原文分类信息
        /// </summary>
        public int DeleteCategory(decimal instid, decimal itemid)
        {
            string cmdText = "select a.ID from DS_Page a, DS_Item b where a.Item_ID = b.ID and b.Inst_ID=:instid and b.ID=:itemid";
            using (var cmd = Db.mkCommand(cmdText, instid, itemid))
            {
                using (var dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        DeleteDocPage(instid, Transcend.DP3.Db.Database.GetValue<decimal>(dr, "ID"));
                    }
                    cmdText = "delete from DS_Item where Inst_ID=:instid and ID=:itemid";
                    return Db.Execute(cmdText, instid, itemid);
                }
            }
        }

        public int DeleteCategory(decimal itemid)
        {
            var cmdText = "select Inst_ID from DS_Item where ID=:itemid";
            var instId = 0M;
            Db.QueryField(ref instId, cmdText, itemid);
            return DeleteCategory(instId, itemid);
        }

        /// <summary>
        /// 删除原文页
        /// </summary>
        public int DeleteDocPage(decimal instId, decimal imgno)
        {
            using (var store = Transcend.Archive.CSScan.Stores.StoreFactory.GetStore(Db.Connection, imgno, string.Empty))
            {
                if (store.Delete(instId, imgno) > 0)
                {
                    string cmdText = "delete from DS_Page where ID=:imgno and Status=1";
                    return Db.Execute(cmdText, imgno);
                }
            }

            return 0;
        }

        public int DeleteDocPage(decimal imgno)
        {
            var sql = "select Inst_ID from DS_Item where ID=(select Item_ID from DS_Page where ID=:imgno)";
            var instid = 0M;
            Db.QueryField(ref instid, sql, imgno);
            return DeleteDocPage(instid, imgno);
        }

        #endregion

        #region --------------------------- 原文读取

        /// <summary>
        /// 返回指定档案的原文信息
        /// </summary>
        public DocInstance GetInstance(string pkvalue, decimal apptypeid, bool withData = false)
        {

            string cmdText = "SELECT PK_VALUE,STORE_ID,FOLDER,TOTAL_SIZE,DATA_SOURCE,DOC_NO,STATUS,RECORD_KEY,VOL_NO,IS_DEL,DEL_DATE,PAGE_COUNT,APP_ID,ID,CREATE_USER_ID,MODIFY_USER_ID,CREATE_DATE,MODIFY_DATE FROM CS_SCAN.DS_INSTANCE where Pk_Value=:instid and APP_ID=:apptypeid";
            var inst = Db.QueryObject(instMapper, cmdText, pkvalue, apptypeid);

            if (inst == null) return null;

            cmdText = "select * from DS_Item where Inst_ID = :instid";
            inst.Add(Db.QueryList(cateMapper, cmdText, inst.Id));

            cmdText = "select * from DS_Page where Item_Id=:itemid and Status = 1";
            foreach (var c in inst.Category)
            {
                c.Add(Db.QueryList(pageMapper, cmdText, c.Id));

                // 返回原文文档数据
                if (withData)
                {
                    foreach (var p in c.DocPage)
                    {
                        p.Data = GetPageData(inst.Id, p.Id);
                    }
                }
            }

            return inst;
        }

        public DocInstance GetInstance(decimal instId, bool withData = false)
        {
            var sql = "select Pk_Value,APP_ID from DS_Instance where ID=:instid";
            var arr = Db.QueryRowAsArray(sql, instId);
            return GetInstance((string)arr[0], (decimal)arr[1], withData);
        }

        public DocInstance GetInstanceByCategoryId(decimal itemid, bool withData = false)
        {
            var sql = "select Inst_ID from DS_Item where id=:itemid";
            var instId = 0M;
            Db.QueryField(ref instId, sql, itemid);
            return GetInstance(instId, withData);
        }

        /// <summary>
        /// 获取指定分类ID下的所有文档页
        /// </summary>
        public DocPage GetPage(decimal imgno, bool retDocData)
        {
            List<DocPage> pages = null;

            string cmdText = "select * from DS_P a where ID = :imgno and Status = 1";
            pages = Db.QueryList(pageMapper, cmdText, imgno);
            DocPage p = null;

            if (pages.Count > 0) p = pages[0];

            if (retDocData && p != null)
            {
                cmdText = "select Inst_ID from dsitem where ID=:itemid";
                var instId = 0M;
                Db.QueryField<decimal>(ref instId, cmdText, p.ItemId);
                p.Data = GetPageData(instId, p.Id);
            }

            return p;
        }

        /// <summary>
        /// 取指定原文页数据
        /// </summary>
        public byte[] GetPageData(decimal instid, decimal imgno)
        {
            var sql = "select Raw_File_Name,(select Folder from DS_Instance a, DS_Item b where a.ID=b.Inst_ID and b.ID=c.Item_ID) uri from DS_Page c where ID=:imgno";
            var arr = Db.QueryRowAsArray(sql, imgno);
            using (var store = Transcend.Archive.CSScan.Stores.StoreFactory.GetStore(Db.Connection, imgno, string.Empty))
            {
                var scanImage = new Transcend.Archive.CSScan.Store.Contract.ScanImage()
                {
                    InstanceId = instid,
                    ImageNo = imgno,
                    FileName = Transcend.DP3.Helper.TypeHelper.Try<string>(arr[0], string.Empty),
                    Tag = Transcend.DP3.Helper.TypeHelper.Try<string>(arr[1], string.Empty)
                };
                return store.GetImage(scanImage.InstanceId, scanImage.ImageNo);
            }
        }

        public byte[] GetPageData(decimal imgno)
        {

            var sql = "select Inst_ID from DS_Item where ID=(select Item_ID from DS_Page where ID=:imgno)";
            var instid = 0M;
            Db.QueryField<decimal>(ref instid, sql, imgno);
            return GetPageData(instid, imgno);

        }

        /// <summary>
        /// 取原文分类，不返回原文文档数据
        /// </summary>
        public DocCategory GetItem(decimal instid, string categoryName)
        {
            DocCategory cate = null;
            string cmdText = "select * from DS_Item where Inst_ID=:instid and Cred_Name=:credname";
            cate = Db.QueryObject(cateMapper, cmdText, instid, categoryName);
            return cate;
        }

        /// <summary>
        /// 取原文分类，不返回原文文档数据
        /// </summary>
        public DocCategory GetItem(decimal instid, decimal itemid)
        {
            DocCategory cate = null;
            string cmdText = "select * from DS_Item where Inst_ID=:instid and ID=:itemid";
            cate = Db.QueryObject(cateMapper, cmdText, instid, itemid);
            return cate;
        }

        #endregion

        #region --------------------------- 原文保存

        /// <summary>
        /// 新增电子文档信息，如果存在图像分类信息，将自动保存。
        /// </summary>
        /// <returns>已有文档分类/新增的文档分类</returns>
        public DocInstance SaveInstance(DocInstance inst)
        {
            if (ImageStorage != null)
            {
                ImageStorage.Dispose();
                ImageStorage = null;
            }
            ImageStorage = Transcend.Archive.CSScan.Stores.StoreFactory.GetDsStore(Db.Connection, inst.Pk, inst.AppTypeId, string.Empty, inst.Id);

            string cmdText = string.Empty;


            //没有指定文档分类ID，自动生成文档分类ID
            if (inst.Id == 0)
            {
                cmdText = "SELECT SEQ_DS_INSTANCE.nextval from dual";
                var instId = 0M;
                Db.QueryField<decimal>(ref instId, cmdText);
                inst.Id = instId;

                //保存文档分类信息
                //如果没有分类名称，将生成一个默认分类
                cmdText = "INSERT INTO CS_SCAN.DS_INSTANCE (PK_VALUE,STORE_ID,TOTAL_SIZE,DOC_NO,RECORD_KEY,VOL_NO,IS_DEL,PAGE_COUNT,APP_ID,ID,CREATE_USER_ID,CREATE_DATE,FOLDER,DIVISION_ID)";
                cmdText += "VALUES ( :PK_VALUE,:STORE_ID,:TOTAL_SIZE,:DOC_NO,:RECORD_KEY,:VOL_NO,:IS_DEL,:PAGE_COUNT,:APP_ID,:ID,:CREATE_USER_ID,sysdate,:FOLDER,:DIVISION_ID)";
                if (!inst.isPartition)
                {
                    cmdText.Replace(",DIVISION_ID", "").Replace(",:DIVISION_ID", "");
                    Db.Execute(cmdText,
                         inst.Pk,
                         ImageStorage.StoreId,
                         inst.Size,
                         inst.DocNo,
                         inst.DocId,
                         inst.VolNo,
                         inst.IsDeleted,
                         inst.Pages,
                         inst.AppTypeId,
                         inst.Id,
                         inst.LastOperater,
                       "/" + inst.Id.ToString() + "/"
                         );
                }
                else
                {
                    Db.Execute(cmdText,
                          inst.Pk,
                          ImageStorage.StoreId,
                          inst.Size,
                          inst.DocNo,
                          inst.DocId,
                          inst.VolNo,
                          inst.IsDeleted,
                          inst.Pages,
                          inst.AppTypeId,
                          inst.Id,
                          inst.LastOperater,
                          inst.DIVISION_ID,
                        "/" + inst.Id.ToString() + "/"
                          );
                }
            }
            else
            {
                cmdText = "update CS_SCAN.DS_INSTANCE set PK_VALUE=:PK_VALUE,STORE_ID=:STORE_ID,TOTAL_SIZE=:TOTAL_SIZE,DOC_NO=:DOC_NO,RECORD_KEY=:RECORD_KEY,VOL_NO=:VOL_NO,IS_DEL=:IS_DEL,PAGE_COUNT=:PAGE_COUNT,APP_ID=:APP_ID,MODIFY_USER_ID=:MODIFY_USER_ID,MODIFY_DATE=sysdate,FOLDER=:FOLDER where ID=:instid";
                Db.Execute(cmdText,
                      inst.Pk,
                      ImageStorage.StoreId,
                      inst.Size,
                      inst.DocNo,
                      inst.DocId,
                      inst.VolNo,
                      inst.IsDeleted,
                      inst.Pages,
                      inst.AppTypeId,
                      inst.LastOperater,
                       "/" + inst.Id.ToString() + "/",
                      inst.Id
                    );
            }




            //保存图像分类
            if (inst.Category != null && inst.Category.Count > 0)
            {
                foreach (var c in inst.Category)
                {
                    c.InstId = inst.Id;
                    c.Document = inst;
                    if (c.DocPage != null)
                        c.PageNum = c.DocPage.Count;
                    SaveCategory(c);
                }
            }

            return inst;
        }

        /// <summary>
        /// 新增文档分类，如果文档分类已存在则将返回现有文档分类，如果不存在则将新增分类
        /// 如果该分类下带有文档信息，该方法会追加文档信息到该文档分类
        /// </summary>
        /// <returns>已有文档分类/新增的文档分类</returns>
        public DocCategory SaveCategory(DocCategory c)
        {


            string cmdText = string.Empty;

            DocCategory cate = GetItem(c.InstId, c.Name);
            if (cate != null)
            {

                cate.Add(c.DocPage);
                c = cate;
            }
            //没有指定文档分类ID，自动生成文档分类ID
            else
            {
                //查询文档分类
                cmdText = @"SELECT ID,PAGE_COUNT,CRED_TYPE,IS_DOUBLE
                            FROM CS_SCAN.DS_CRED_TYPE WHERE CRED_NAME=:CRED_NAME AND IS_VALID=1";
                var cred = Db.QueryRowAsDictionary(cmdText, c.Name);
                if (cred != null && cred.Count > 0)
                {
                    c.CredId = decimal.Parse(cred["ID"].ToString());
                    c.CredType = decimal.Parse(cred["CRED_TYPE"].ToString());
                    //   c.PageNum = int.Parse(cred["PAGE_COUNT"].ToString());
                    c.IsDouble = int.Parse(cred["IS_DOUBLE"].ToString());
                }
                else
                {
                    //新增文档分类
                    cmdText = "SELECT SEQ_DS_CRED_TYPE.nextval from dual";
                    var credid = 0M;
                    Db.QueryField<decimal>(ref credid, cmdText);
                    c.CredId = credid;
                    cmdText = @"INSERT INTO CS_SCAN.DS_CRED_TYPE (ID,CRED_NAME,IS_VALID,CRED_TYPE,PAGE_COUNT,IS_DOUBLE,CREATE_USER_ID,CREATE_DATE)
                                         VALUES ( :ID,:CRED_NAME,1,:CRED_TYPE,:PAGE_COUNT,:IS_DOUBLE,:CREATE_USER_ID,SYSDATE)";
                    Db.Execute(cmdText, c.CredId, c.Name, c.CredType, c.PageNum, c.IsDouble, c.Document.LastOperater);
                }


                cmdText = "SELECT SEQ_DS_ITEM.nextval from dual";
                var itemid = 0M;
                Db.QueryField<decimal>(ref itemid, cmdText);
                c.Id = itemid;
                //保存文档分类信息
                //如果没有分类名称，将生成一个默认分类             
                cmdText = @"INSERT INTO CS_SCAN.DS_ITEM (ROW_NO,CRED_ID,CRED_NAME,CRED_TYPE,PAGE_COUNT,COPIES,SCAN_NUM,STEP_ID,IS_DOUBLE,NOTES,ID,INST_ID,CREATE_USER_ID,CREATE_DATE,DIVISION_ID)
                           VALUES ( :ROW_NO,:CRED_ID,:CRED_NAME,:CRED_TYPE,:PAGE_COUNT,:COPIES,:SCAN_NUM,:STEP_ID,:IS_DOUBLE,:NOTES,:ID,:INST_ID,:CREATE_USER_ID,sysdate,:DIVISION_ID)";
                if (!c.Document.isPartition)
                {
                    cmdText.Replace(",DIVISION_ID", "").Replace(",:DIVISION_ID", "");
                    Db.Execute(cmdText, c.RowNo, c.CredId, c.Name, c.CredType, c.PageNum, c.Copies, c.ScanNum, c.STEP_ID, c.IsDouble, c.Notes, c.Id, c.InstId, c.Document.LastOperater);
                }
                else
                {
                    Db.Execute(cmdText, c.RowNo, c.CredId, c.Name, c.CredType, c.PageNum, c.Copies, c.ScanNum, c.STEP_ID, c.IsDouble, c.Notes, c.Id, c.InstId, c.Document.LastOperater, c.Document.DIVISION_ID);
                }
            }

            foreach (DocPage p in c.DocPage)
            {
                if (p.ItemId == 0) p.ItemId = c.Id;
                p.Category = c;
                SavePage(p);
            }

            return c;
        }

        /// <summary>
        /// 新增文档页
        /// 如果主键值=0将会自生成主键
        /// </summary>
        /// <returns></returns>
        public DocPage SavePage(DocPage p)
        {

            string cmdText = string.Empty;

            //没有文档页ID，自动生成文档页ID
            if (p.Id == 0)
            {
                cmdText = "select SEQ_DS_PAGE.nextval from dual";
                var imgNo = 0M;
                Db.QueryField<decimal>(ref imgNo, cmdText);
                p.Id = imgNo;
                //插入dspage表中
                cmdText = @"INSERT INTO CS_SCAN.DS_PAGE (ID,PAGE_NO,STATUS,IMAGE_SIZE,PAGE_NUM,RAW_FILE_NAME,USER_ID,ITEM_ID,CREATE_USER_ID,CREATE_DATE,DIVISION_ID)
                             VALUES ( :ID,:PAGE_NO,:STATUS,:IMAGE_SIZE,:PAGE_NUM,:RAW_FILE_NAME,:USER_ID,:ITEM_ID,:CREATE_USER_ID,sysdate,:DIVISION_ID)";

                if (!p.Category.Document.isPartition)
                {
                    cmdText.Replace(",DIVISION_ID", "").Replace(",:DIVISION_ID", "");
                    Db.Execute(cmdText,
                               p.Id,
                               p.PageNo,
                               p.Status,
                               p.Size,
                               p.PageNum,
                               p.RawFileName,
                               p.UserId,
                               p.ItemId,
                               p.Category.Document.LastOperater
                               );
                }
                else
                {
                    Db.Execute(cmdText,
                        p.Id,
                        p.PageNo,
                        p.Status,
                        p.Size,
                        p.PageNum,
                        p.RawFileName,
                        p.UserId,
                        p.ItemId,
                        p.Category.Document.LastOperater,
                        p.Category.Document.DIVISION_ID
                        );
                }
            }

            SavePageData(p);
            return p;
        }

        /// <summary>
        /// 向
        /// </summary>
        /// <param name="pages"></param>
        /// <returns></returns>
        public IList<DocPage> SavePages(IList<DocPage> pages)
        {
            if (pages != null)
            {
                for (int i = 0; i < pages.Count; i++)
                {
                    pages[i] = SavePage(pages[i]);
                }
            }
            return pages;
        }

        /// <summary>
        /// 将文档数据保存到文档表中，如果文档已经存在，则更新，文档不存在则插入
        /// 注意：保存文档数据之前应该在dspage插入文档信息，不然将出错
        /// </summary>
        public void SavePageData(DocPage p)
        {
            byte[] fileData = p.Data;
            if (!string.IsNullOrEmpty(p.LocalPath))
            {
                FileInfo fi = new FileInfo(p.LocalPath);
                if (!fi.Exists)
                {
                    throw new ArgumentException(string.Format("文件未找到{0}", p.LocalPath));
                }
                fileData = new byte[fi.Length];
                byte[] buf = new byte[10240];
                int read = 0;
                using (MemoryStream ms = new MemoryStream(fileData))
                {
                    using (FileStream fs = File.OpenRead(p.LocalPath))
                    {
                        while ((read = fs.Read(buf, 0, buf.Length)) > 0)
                        {
                            ms.Write(buf, 0, read);
                        }
                    }
                }
            }

            if (fileData == null)
            {
                throw new ArgumentException(string.Format("无法取得文档数据{0}", p.LocalPath));
            }

            if (EncryptedImage)     //如果文件已密码，在此处进行解密
            {
                using (var outputStream = new MemoryStream())
                {
                    using (var inputStream = new MemoryStream(fileData))
                    {
                        var des = new Transcend.Utility.Security.TripleDES.TripleDESService()
                        {
                            Password = ImagePassword
                        };
                        des.DecryptFile(inputStream, outputStream);
                        fileData = outputStream.ToArray();

                    }
                }
            }

            var scanImage = new Transcend.Archive.CSScan.Store.Contract.ScanImage();

            scanImage.InstanceId = p.Category.Document.Id;
            scanImage.Tag = p.Category.Document.Uri;
            scanImage.ImageNo = p.Id;
            scanImage.Content = fileData;
            scanImage.FileName = p.RawFileName;

            ImageStorage.Add(scanImage);

            //ImageStorage.Save();

        }
        /// <summary>
        /// 保存图像
        /// </summary>
        public void UploadImage()
        {
            if (ImageStorage == null) return;
            ImageStorage.Save();
            ImageStorage.Dispose();
            ImageStorage = null;
        }
        #endregion

    }
}
